package picture.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import MyDaily.dao.DBconnection;
import MyDaily.pojo.Picture;
import MyDaily.pojo.User;

/**
* @author 作者:lin
* @version 创建时间：2022年3月22日 下午8:34:18
* 类说明
*/
public class picture {
	//新增图片
	public static int Insertpicture(Picture picture) {
		// 创建Connection
		Connection conn = null;
		// 创建PreparedStatement
		PreparedStatement pStat = null;
		try {
			// 定义sql语句
			String sql = "INSERT INTO `picture`(name,imgurl,date) VALUES(?,?,?)";
			// 获取连接
			conn = DBconnection.getConnection();
			// 编译sql
			pStat = conn.prepareStatement(sql);
			// 通过User实体类给sql语句赋值
			pStat.setString(1, picture.getName());
			pStat.setString(2, picture.getImgurl());
			pStat.setTimestamp(3, picture.getDate());
			// 执行sql，并返回执行结果
			return pStat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (pStat != null) {
					pStat.close();
				}

				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public static List<Picture> Queryimg(String name) {
		// 创建Connection
		Connection conn = null;
		// 创建PreparedStatement
		PreparedStatement pStat = null;
		// 创建ResultSet
		ResultSet rSet = null;
		// 创建Lsit集合储存rs结果集
		List<Picture> PictureList = new ArrayList<Picture>();
		try {
			// 定义sql语句
			String sql = "SELECT * FROM `picture` WHERE `name` = '"+name+"' ";
			// 获取连接
			conn = DBconnection.getConnection();
			// 编译sql
			pStat = conn.prepareStatement(sql);
			// 执行sql，将查询结果放在结果集中
			rSet = pStat.executeQuery();
			// 判断是否有数据
			while (rSet.next()) {
				// 初始化实体类，并将查询结果添加user属性中
				Picture picture = new Picture();
				picture.setId(rSet.getInt("id"));
				picture.setName(rSet.getString("name"));
				picture.setImgurl(rSet.getString("imgurl"));
				picture.setDate(rSet.getTimestamp("date"));
				PictureList.add(picture);
			}
			return PictureList;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (pStat != null) {
					pStat.close();
				}

				if (conn != null) {
					conn.close();
				}
				if (rSet != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return null;
	}
	//删除图片
	public static int deleteimg(int id) {
		Connection conn = null;
		PreparedStatement pStat = null;
		String sql="delete from picture where id = "+id+"";
		try {
			conn = DBconnection.getConnection();
			pStat = conn.prepareStatement(sql);
			return pStat.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				// 关闭资源
				if (pStat != null) {
					pStat.close();
				}

				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
}

